package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import com.zr.model.Course;
import com.zr.model.Record;
import com.zr.model.User;
import com.zr.util.JDBCUtil;

public class CourseDao {
/**
 * 查询所有课程记录信息
 * @return  所有课程记录
 */
	public List<Record>  findAllRecord(int start,int rows,int sid){
		List<Record>  records = new ArrayList<Record>();
		StringBuffer str =new StringBuffer("select student.sid,cname,cbtime,cetime,ccount,tname,sname,course.cid ");
		str.append(" from record,course,teacher,student where course.tid=teacher.tid ");
		str.append("  AND record.sid=student.sid AND record.cid=course.cid and student.sid=? limit ?,? ");
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(str.toString());
			pst.setInt(2, start);
			pst.setInt(3, rows);
			pst.setInt(1, sid);
			ResultSet  rs = pst.executeQuery();			
			while(rs.next()){
				Record record= new Record
			(rs.getInt("cid"),rs.getInt("sid"), rs.getString("cname"), rs.getString("cbtime"), 
			rs.getString("cetime"),rs.getInt("ccount") , rs.getString("tname"), rs.getString("sname"));
				records.add(record);
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  records;
	}	
/**
 * 查询所有课程记录总数
 * @return 所有课程记录总数
 */
	public int  findAllRecordCount(int sid){
		int rcount = 0;
		StringBuffer str =new StringBuffer("select count(*) rcount from record,course,teacher,student");
		str.append(" where course.tid=teacher.tid AND record.cid=course.cid ");
		str.append("AND record.sid=student.sid and record.sid=?");		
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(str.toString());
			pst.setInt(1, sid);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				rcount = rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  rcount;
	}
	/**
	 * 查询所有课程信息
	 * @return 所有课程
	 */
	public List<Course>  findAllCourse(int start,int rows){
		List<Course>  courses = new ArrayList<Course>();
		StringBuffer str =new StringBuffer("select cid,cname,cbtime,cetime,ccount,tname");
		str.append(" from course,teacher where course.tid=teacher.tid limit ?,?");
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(str.toString());
			pst.setInt(1, start);
			pst.setInt(2, rows);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				Course course= new Course
			(rs.getInt("cid"), rs.getString("cname"), rs.getString("cbtime"), 
			rs.getString("cetime"),rs.getString("ccount") , rs.getString("tname"));
				courses.add(course);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  courses;
	}
/**
 * 查询所有课程总数
 * @return 所有课程总数
 */
	public int  findAllCourseCount(){
		int acount = 0;
		String sql ="select count(*) acount from course,teacher where course.tid=teacher.tid";
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				acount = rs.getInt("acount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  acount;
	}
	/** 
	 * 搜索课程的总数
	 * @param value 传入搜索关键字
	 * @return
	 */
	public int selCourseCount(String value){
		int ccount=0;
		StringBuffer str =new StringBuffer("select count(cid) ccount from course,teacher ");
		str.append(" WHERE course.tid=teacher.tid  and (tname like ? OR cname like ?)");
		Connection con = JDBCUtil.getJDBConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(str.toString());
			pst.setString(1, "%"+value+"%");
			pst.setString(2, "%"+value+"%");

			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				ccount = rs.getInt("ccount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		
		return ccount;
		
	}	
	/** 
	 * 搜索课程的方法 
	 * @param start 
	 * @param rows
	 * @param value 传入搜索的关键字
	 * @return 返回课程
	 */
	public List<Course> selCourse(String value){
		List<Course> courses = new ArrayList<Course>();
		StringBuffer str =new StringBuffer("select cid,cname,cbtime,cetime,ccount,tname from course,teacher");
		str.append(" WHERE course.tid=teacher.tid and (tname like ? OR cname like ?)");
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(str.toString());
			pst.setString(1, "%"+value+"%");
			pst.setString(2, "%"+value+"%");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Course course= new Course
			(rs.getInt("cid"), rs.getString("cname"), rs.getString("cbtime"), 
			rs.getString("cetime"),rs.getString("ccount") , rs.getString("tname"));
				courses.add(course);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}		
		return courses;		
	}	
	/**
	 * 删除课程的方法
	 * @param cid 传入需要删除的课程Id
	 * @return  返回是否删除成功
	 */
	public int  delRecordByCid(int cid){
		int cd=0;
		String sql = "delete from record where cid=?";
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			pst.setInt(1, cid);			
			int i=pst.executeUpdate();
			if(i>0){
				cd=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  cd;
	}
	/**
	 * 提供选择课程的方法
	 * @param cid 传入需要增加课程的ID
	 * @param sid 传入需要增加学生的ID
	 * @return
	 */
	public int addRecordByCidAndSid(int cid,int sid){
		int cd=0;
		Connection con =null;
		String sql ="insert into record (cid,sid) values (?,?)";					
		try {
			con =JDBCUtil.getJDBConnection();
			PreparedStatement pst =con.prepareStatement(sql);
			pst.setInt(1, cid);
			pst.setInt(2, sid);
			int i =pst.executeUpdate();				
			if(i>0){
				cd=1;				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cd;		
	}
	

//	/**
//	 * 对数据库中 course是否存在该课程
//	 * @param cid 传入课程 ID
//	 * @return 有返回 true
//	 */
//	public boolean validataCid(int cid,int sid){
//		boolean flag =false;
//		String sql ="select * from record where cid =?";
//		
//		Connection con =JDBCUtil.getJDBConnection();
//		try {
//			PreparedStatement pst=con.prepareStatement(sql);
//			pst.setInt(1, cid);
//			ResultSet rs=pst.executeQuery();
//			if(rs.next()){
//				flag=true;
//			}			
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return flag;		
//	}

}
